package com.lance.testing.addressbook;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.ArrayList;

public class PersonQueries implements PersonInterface
{

    private Connection connection = null;
    private PreparedStatement selectAllPeople = null;
    private PreparedStatement selectPeopleByLastName = null;
    private PreparedStatement insertNewPerson = null;

    public PersonQueries()
    {
        String[] types =
        {
            "TABLE"
        };
        try
        {
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:Addresses.db");
            //Class.forName("com.mysql.jdbc.Driver").newInstance();
            //connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            //connection = DriverManager.getConnection("jdbc:mysql://localhost/addresses?" + "user=lance&password=lance");

            Statement batchStatement = connection.createStatement();
            batchStatement.addBatch("DROP TABLE IF EXISTS Addresses;");
            batchStatement.addBatch("CREATE TABLE Addresses ("
                    + "AddressID INTEGER PRIMARY KEY, "
                    + "FirstName VARCHAR (15) NOT NULL, "
                    + "LastName VARCHAR (30) NOT NULL, "
                    + "Email VARCHAR (30) NOT NULL, "
                    + "PhoneNumber VARCHAR (15) NOT NULL );");
            batchStatement.addBatch("INSERT INTO Addresses (FirstName,LastName,Email,PhoneNumber)"
                    + "VALUES ('Mike','Green','demo1@deitel.com','555-5555');");
            batchStatement.addBatch("INSERT INTO Addresses (FirstName,LastName,Email,PhoneNumber)"
                    + "VALUES ('Mary','Brown','demo2@deitel.com','555-1234');");
            batchStatement.executeBatch();
            DatabaseMetaData metaData = connection.getMetaData();
            System.out.println(metaData.getConnection());

            selectAllPeople = connection.prepareStatement("SELECT * FROM Addresses");

            selectPeopleByLastName = connection.prepareStatement("SELECT * FROM Addresses WHERE LastName = ?");

            insertNewPerson = connection.prepareStatement("INSERT INTO Addresses "
                    + "( FirstName, LastName, Email, PhoneNumber ) "
                    + "VALUES ( ?, ?, ?, ? )");
        }
        catch (ClassNotFoundException ex)
        {
            System.out.println(ex.toString());
        }
        catch (SQLException sqlException)
        {
            System.out.println(sqlException.toString());
            System.exit(1);
        }
    }

    @Override
    public List<Person> getAllPeople()
    {
        List<Person> results = null;
        ResultSet resultSet = null;

        try
        {
            resultSet = selectAllPeople.executeQuery();

            results = new ArrayList<Person>();

            while (resultSet.next())
            {
                results.add(new Person(
                        resultSet.getInt("addressID"),
                        resultSet.getString("firstName"),
                        resultSet.getString("lastName"),
                        resultSet.getString("email"),
                        resultSet.getString("phoneNumber")));
            }
        }
        catch (SQLException sqlException)
        {
            System.out.println(sqlException.toString());
        }
        finally
        {
            try
            {
                resultSet.close();
            }
            catch (SQLException sqlException)
            {
                System.out.println(sqlException.toString());
                close();
            }
        }

        return results;
    }

    @Override
    public List<Person> getPeopleByLastName(String name)
    {
        List<Person> results = null;
        ResultSet resultSet = null;

        try
        {
            selectPeopleByLastName.setString(1, name);

            resultSet = selectPeopleByLastName.executeQuery();

            results = new ArrayList<Person>();

            while (resultSet.next())
            {
                results.add(new Person(
                        resultSet.getInt("AddressID"),
                        resultSet.getString("firstName"),
                        resultSet.getString("lastName"),
                        resultSet.getString("email"),
                        resultSet.getString("phoneNumber")));
            }
        }
        catch (SQLException sqlException)
        {
            System.out.println(sqlException.toString());
        }
        finally
        {
            try
            {
                resultSet.close();
            }
            catch (SQLException sqlException)
            {
                System.out.println(sqlException.toString());
                close();
            }
        }

        return results;
    }

    @Override
    public int addPerson(String fname, String lname, String email, String num)
    {
        int result = 0;

        try
        {
            insertNewPerson.setString(1, fname);
            insertNewPerson.setString(2, lname);
            insertNewPerson.setString(3, email);
            insertNewPerson.setString(4, num);

            result = insertNewPerson.executeUpdate();
        }
        catch (SQLException sqlException)
        {
            System.out.println(sqlException.toString());
            close();
        }

        return result;
    }

    @Override
    public void close()
    {
        try
        {
            connection.close();
        }
        catch (SQLException sqlException)
        {
            System.out.println(sqlException.toString());
        }
    }
}